/**
 * @Copyright: Interactive Business Solution Pte Ltd, Singapore
 * @Author: Li Wen, 2007-11-19
 * @Version: 071119.001
 * Data access object of package class 
 */
package com.ibsol.database
{
	import com.ibsol.model.TourPackage;
	
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	
	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
	
	public class PackageDAO
	{
		//User level
		private static var MANAGER_LEVEL:int = 0;
		private static var ADMIN_LEVEL:int = 1;
		private static var SALESMAN_LEVEL:int = 2;
		//Database connection
		private var _tourixDBConn:SQLConnection;
		/**
		 * Package default constructor, gets tourix database connection
		 * @param: null
		 */
		public function PackageDAO()
		{
			_tourixDBConn = DBConnManager.getConnection("tourix");
		}
		/**
		 * Loads specific package according to package id
		 * @param: packageid:int			package id
		 * 		   resultHandler:Function	result function adapter, return package object
		 * 		   faultHandler:Function	fault function adapter
		 * @return: void
		 */
		public function loadPackage(packageid:int, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT * FROM package WHERE id=" + packageid + " AND offlineOperator != 'DEL'";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function(event:SQLEvent):void
				{
					resultHandler.call(this, typeObject(sqlStr.getResult().data[0]));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Loads all packages information from local database
		 * @param: resultHandler:Function	result function adapter, return package array colleciton
		 * 		   faultHandler:Function	error handler adapter
		 * @return: void
		 */
		public function loadPackages(resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT * FROM package WHERE offlineOperator != 'DEL'";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Loads all packages information from local database by duration
		 * @param: duration:int				duration provided by query
		 *		   resultHandler:Function	result function adapter, return specific duration package array collection
		 *		   faultHandler:Function	error handler adapter
		 * @return: void
		 */
		public function loadPackagesByDuration(duration:int, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT * FROM package WHERE offlineOperator !='DEL' and duration=" + duration;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Loads all packages information from local database by package name
		 * @param: name:String				package name string provided by query
		 * 		   resultHandler:Function	result function adapter, return package array collection whose name contains
		 * 									specific text
		 * 		   faultHandler:Function	error handler adapter
		 * @return: void
		 */
		public function loadPackagesByName(name:String, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT * FROM package WHERE offlineOperator != 'DEL' and name LIKE '%" + name + "%'";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Loads all packages information from local database by package price. Such as, input price 2000, all packages in
		 * price range 1500 ~ 2500 will be loaded
		 * @param: price:Number				package price provided by query
		 * 		   resultHandler:Function	result function adapter, return package array collection whose price is in range
		 * 									(price - 500) ~ (price + 500)
		 * 		   faultHandler:Function	error handler adapter
		 * @return: void
		 */
		public function loadPackagesByPrice(price:Number, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT * FROM package WHERE offlineOperator != 'DEL' and price BETWEEN " + (price - 500) + " AND " + (price + 500);
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Loads all packages information from local database by multiple query inputs.
		 * Type are: name, duration and price
		 * @param: resultHandler:Function	result function adapter, return package array collection satisfy input parameters
		 * 		   faultHandler:Function	error handler adapter
		 * 		   name:String				input parameter for name, default is ""
		 * 		   duration:int				input parameter for duration, default is 0
		 * 		   price:Number				input parameter for price, default is 0
		 * @return: void
		 */
		public function loadPackagesByRandom(resultHandler:Function, faultHandler:Function = null, name:String = "", duration:int = 0, price:Number = 0):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT * FROM package WHERE offlineOperator !='DEL' and name LIKE '%" + name + "%'";
			if(duration != 0)
				sqlStr.text += (" AND duration=" + duration);
			if(price != 0)
				sqlStr.text += (" AND (price BETWEEN " + (price - 500) + " AND " + (price + 500) + ")");
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
	 	 * 得到热门的最多8条旅游套装，没有则显示无数据 1代表没有数据 2代表有数据
	 	 * @param resultHandler 返回结果集的方程
	 	 **/
	 	public function getPackageListByIsHot(resultHandler:Function):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
	 		sqlStr = new SQLStatement();
	 		sqlStr.sqlConnection = _tourixDBConn;
	 		sqlStr.text="Select * from package where ishot<>0 limit 8";
	 		sqlStr.addEventListener(SQLEvent.RESULT,function (e:SQLEvent):void{
	 			var ac:ArrayCollection = typeArray(sqlStr.getResult().data);
	 			if(ac == null || ac.length==0){
	 				ac = new ArrayCollection();
	 				ac.addItem("1");
	 				ac.addItem("暂无数据");
	 			}
	 			else
	 				ac.addItemAt("2",0);
	 			resultHandler.call(this,ac);
	 		});
	 		sqlStr.addEventListener(SQLErrorEvent.ERROR,function (e:SQLErrorEvent):void{
	 			var ac:ArrayCollection=new ArrayCollection();
	 			ac.addItem("1");
	 			ac.addItem("暂无数据");
	 			resultHandler.call(this,ac);
	 		});
			sqlStr.execute();
	    }
	    /**
	 	 * 最多8条推荐路线
	 	 * @param resultHandler 返回结果集的方程
	 	 * @param faultHandler 错误处理方程
	 	 **/
		public function getPackageListByIsProm(resultHandler:Function,faultHandler:Function=null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
	 		sqlStr = new SQLStatement();
	 		sqlStr.sqlConnection = _tourixDBConn;
	 		sqlStr.text="Select * from package where isProm<>0 limit 8";
	 		sqlStr.addEventListener(SQLEvent.RESULT,function (e:SQLEvent):void{
	 			resultHandler.call(this,typeArray(sqlStr.getResult().data));
	 		});
	 		sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler==null ? sqlErrorHandler : faultHandler);
	 		sqlStr.execute();
	 	}
		/**
		 * Creates a new tour package into database
		 * package (id, name, duration, price, isProm, thumbPic, xml, category, offlineOperator)
		 * @param:	pacakgeObj:Package			new package object will be inserted into databasae
		 * 			resultHandler:Function		result handler adapter
		 * @return:	void
		 */
		public function insertPackage(packageObj:TourPackage, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "INSERT INTO package (name, duration, price, isProm, thumbPic, xml, category, offlineOperator, updateTime, isHot) " + 
					"VALUES ('" + packageObj.name + "'," + packageObj.duration + "," + packageObj.price + "," + String(packageObj.isProm?1:0) + ",'" + 
					packageObj.thumbPic + "','" + packageObj.xml + "','" + packageObj.category + "','NEW','T'," + packageObj.isHot + ");";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					packageObj.id = sqlStr.getResult().lastInsertRowID;
					if(resultHandler != null)
						resultHandler.call(this, packageObj.id);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Updates a package information into database
		 * @param:	packageObj:Package			package needed to be updated
		 * 			resultHandler:Function		result handler adapter
		 * 			faultHandler:Function		fault handler adapter
		 * @return:	void
		 */
		public function updatePackage(packageObj:TourPackage, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE package SET name='" + packageObj.name + "',duration=" + packageObj.duration + ",price=" + packageObj.price + ",isProm=" + String(packageObj.isProm?1:0) + 
				",thumbPic='" + packageObj.thumbPic + "',xml='" + packageObj.xml + "',category='" + packageObj.category + "',offlineOperator='EDIT',updateTime='T', isHot=" + 
				(packageObj.isHot?1:0) + " WHERE id=" + packageObj.id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Deletes packages offline, just updates package offlinOperator to "DEL"
		 * @param:	id:int					package id, whom will be deleted
		 * 			resultHandler:Function	result handler adapter
		 * 			faultHandler:Function	fault handler adapter
		 * @return:	void
		 */
		public function deletePackages(id:int, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE package SET offlineOperator='DEL', updateTime='T' WHERE id=" + id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Permentately delete packages from database, whose offlineOperator is set to "DEL". If user wants to delete photo,
		 * user just updates offlineOperator to "DEL". This function deletes photos permentately from database
		 * @param:	resultHandler:Function		result handler adapter
		 * 			faultHandler:Function		fault handler adapter
		 * @return: void
		 */
		public function clearDeletedPackages(resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "DELETE * FROM package WHERE offlineOperator = 'DEL'";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Default SQL error handler, if above functions' fault handlers are null, this error handler is applied
		 * @param: event:SQLError	SQL Error event will be handled if there is an error
		 * @return: void
		 */
		private function sqlErrorHandler(event:SQLErrorEvent):void
		{
			mx.controls.Alert.show(event.text, "Error");
			trace(event.text);
		}
		/**
		 * Converts database object to package class
		 * @param: o:Object database return object
		 * @return: Package database object after conversion, type Package
		 */
		private function typeObject(o:Object):TourPackage
		{
			var p:TourPackage = new TourPackage();
			p.id = o.id;
			p.name = o.name;
			p.duration = o.duration;
			p.price = o.price;
			p.isProm = o.isProm;
			p.isHot = o.isHot;
			p.thumbPic = o.thumbPic;
			p.xml = o.xml;
			p.category = o.category;
			p.offlineOperator = o.offlineOperator;
			p.updateTime = o.updateTime;
			return p;
		}
		/**
		 * Converts array object to package array collection
		 * @param: a:Array array of objects returned by SQL
		 * @return: ArrayCollection ArrayCollection of package after conversion from Array
		 */
		private function typeArray(a:Array):ArrayCollection
		{
			if (a==null) return null;
			var ac:ArrayCollection = new ArrayCollection();
			for (var i:int; i<a.length; i++)
			{
				ac.addItem(typeObject(a[i]));
			}
			return ac;
		}
	}
}